package com.company.book_store.dao;

import com.company.book_store.pojo.Book;
import com.company.book_store.util.jdbcutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BoodaoImpl implements Ibookdao{
    private Connection con=null;
    @Override
    public List<Book> selectall(int num) {
        con= jdbcutils.getConnection();
        String sql="select * from book limit ?,20";
        PreparedStatement pst=null;
        ResultSet set=null;
        List<Book>list=new ArrayList<>();
        try {
            pst=con.prepareStatement(sql);
            pst.setInt(1,num*20);
            set=pst.executeQuery();
            while(set.next())
            {
                int id=set.getInt("id");
                String book_name=set.getString("book_name");
                String book_author=set.getString("book_author");
                String book_classes=set.getString("book_classes");
                String book_price=set.getString("book_price");
                String book_public=set.getString("book_public");
                int book_surplus=set.getInt("book_surplus");
                Book book=new Book(id,book_name,book_author,book_classes,book_price,book_public,book_surplus);
                list.add(book);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Book> selectlike(int num,String str) {
        con= jdbcutils.getConnection();
        String sql="select * from book where book_name like ? limit ?,10";
        PreparedStatement pst=null;
        ResultSet set=null;
        List<Book>list=new ArrayList<>();
        try {
            pst=con.prepareStatement(sql);
            pst.setString(1,"%"+str+"%");
            pst.setInt(2,num);
            set=pst.executeQuery();
            while(set.next())
            {
                int id=set.getInt("id");
                String book_name=set.getString("book_name");
                String book_author=set.getString("book_author");
                String book_classes=set.getString("book_classes");
                String book_price=set.getString("book_price");
                String book_public=set.getString("book_public");
                int book_surplus=set.getInt("book_surplus");
                Book book=new Book(id,book_name,book_author,book_classes,book_price,book_public,book_surplus);
                list.add(book);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public Book selectBybookid(int num) {
        con= jdbcutils.getConnection();
        String sql="select * from book where id=?";
        PreparedStatement pst=null;
        ResultSet set=null;
        Book res=null;
        try {
            pst=con.prepareStatement(sql);
            pst.setInt(1,num);
            set=pst.executeQuery();
            if(set.next())
            {
                int id=set.getInt("id");
                String book_name=set.getString("book_name");
                String book_author=set.getString("book_author");
                String book_classes=set.getString("book_classes");
                String book_price=set.getString("book_price");
                String book_public=set.getString("book_public");
                int book_surplus=set.getInt("book_surplus");
                res=new Book(id,book_name,book_author,book_classes,book_price,book_public,book_surplus);
            }
            return res;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int updatePlus(int num,int cnt) {
        con= jdbcutils.getConnection();
        String sql="update book set book_surplus=? where id=?";
        PreparedStatement pst=null;
        ResultSet set=null;
        try {
            pst=con.prepareStatement(sql);
            pst.setInt(1,cnt);
            pst.setInt(2,num);
            return pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int deleteBybookid(int num) {
        con= jdbcutils.getConnection();
        String sql="delete from book where id=?";
        PreparedStatement pst=null;
        ResultSet set=null;
        try {
            pst=con.prepareStatement(sql);
            pst.setInt(1,num);
            return pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int addbook(Book book) {
        con=jdbcutils.getConnection();
        PreparedStatement pst=null;
        ResultSet set=null;
        String sql="insert into book(book_name,book_author,book_classes,book_price,book_public,book_surplus)VALUES(?,?,?,?,?,?)";
        try {
            pst=con.prepareStatement(sql);
            pst.setString(1,book.getBook_name());
            pst.setString(2,book.getBook_author());
            pst.setString(3,book.getBook_classes());
            pst.setString(4,book.getBook_price());
            pst.setString(5,book.getBook_public());
            pst.setInt(6,book.getBook_surplus());
            return pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return 0;
    }

    @Override
    public int updatebook(Book book) {
        con=jdbcutils.getConnection();
        PreparedStatement pst=null;
        ResultSet set=null;
        String sql="update book set book_name=?,book_author=?,book_classes=?,book_price=?,book_public=?,book_surplus=? where id=?";
        try {
            pst=con.prepareStatement(sql);
            pst.setString(1,book.getBook_name());
            pst.setString(2,book.getBook_author());
            pst.setString(3,book.getBook_classes());
            pst.setString(4,book.getBook_price());
            pst.setString(5,book.getBook_public());
            pst.setInt(6,book.getBook_surplus());
            pst.setInt(7,book.getId());
            return pst.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return 0;
    }


}
